﻿using System;
using System.Data;
using System.Data.SqlClient;
using Common;

namespace DataAccess
{
    public class CategoryDao
    {
        private SqlConnection _sqlConnection;
        public CategoryDao()
        {
            _sqlConnection = SqlTools.GetConnection();
        }
        /// <summary>
        /// Views all category.
        /// </summary>
        /// <returns></returns>
        public DataTable ViewAllCategory()
        {
            try
            {
                DataSet dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlQuery.ViewAllCategory);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                
                throw;
            }
        }
        /// <summary>
        /// Creates the category.
        /// </summary>
        /// <param name="cateName">Name of the category.</param>
        /// <param name="cateDesc">The category description.</param>
        /// <param name="status">if set to <c>true</c> [status].</param>
        /// <returns></returns>
        public int CreateCategory(string categoryName, string categoryDescription, bool status)
        {
            try
            {
                var parameters = new SqlParameter[3];
                parameters[0] = new SqlParameter("@CateName", SqlDbType.Text) { Value = categoryName };
                parameters[1] = new SqlParameter("@CateDescription", SqlDbType.Text) { Value = categoryDescription };
                parameters[2] = new SqlParameter("@Status", SqlDbType.Bit) { Value = status };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlQuery.CreateCategory);
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        /// <summary>
        /// Updates the category.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        /// <param name="categoryName">Name of the category.</param>
        /// <param name="categoryDescription">The category description.</param>
        /// <param name="status">if set to <c>true</c> [status].</param>
        /// <returns></returns>
        public int UpdateCategory(int categoryId, string categoryName, string categoryDescription, bool status)
        {
            try
            {
                var parameters = new SqlParameter[4];
                parameters[0] = new SqlParameter("@CateId", SqlDbType.Text) { Value = categoryId };
                parameters[1] = new SqlParameter("@CateName", SqlDbType.Text) { Value = categoryName };
                parameters[2] = new SqlParameter("@CateDescription", SqlDbType.Text) { Value = categoryDescription };
                parameters[3] = new SqlParameter("@Status", SqlDbType.Bit) { Value = status };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlQuery.UpdateCategory);
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        /// <summary>
        /// Deletes the category.
        /// </summary>
        /// <param name="cateId">The category id.</param>
        /// <returns></returns>
        public int DeleteCategory(int categoryId)
        {
            try
            {
                var parameter = new SqlParameter("@CateId", SqlDbType.Int) { Value = categoryId };
                return SqlHelper.ExecuteNonQuery(_sqlConnection, CommandType.Text, SqlQuery.DeactivateCategoryById, parameter);
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        /// <summary>
        /// Views the category name by id.
        /// </summary>
        /// <param name="cateId">The category id.</param>
        /// <returns></returns>
        public string ViewCategoryNameById(int categoryId)
        {
            try
            {
                var parameter = new SqlParameter("@CateId", SqlDbType.Int) { Value = categoryId };
                DataSet dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, 
                    SqlQuery.ViewCategoryNameById, parameter);
                return (string)dataSet.Tables[0].Rows[0][0];
            }
            catch (Exception)
            {
                
                throw;
            }
        }
        /// <summary>
        /// Views the name of the category id by.
        /// </summary>
        /// <param name="cateName">Name of the category.</param>
        /// <returns></returns>
        public int ViewCategoryIdByName(string categoryName)
        {
            try
            {
                var parameter = new SqlParameter("@CateName", SqlDbType.Text) { Value = categoryName };
                DataSet dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text, SqlQuery.ViewCategoryIdByName,
                                                           parameter);
                return (int)dataSet.Tables[0].Rows[0][0];
            }
            catch (Exception)
            {
                
                throw;
            }
        }
        /// <summary>
        /// Views the post by category id.
        /// </summary>
        /// <param name="cateId">The category id.</param>
        /// <returns></returns>
        public DataTable ViewPostByCategoryId(int categoryId)
        {
            try
            {
                var parameter = new SqlParameter("@CateId", SqlDbType.Int) {Value = categoryId};
                DataSet dataSet = SqlHelper.ExecuteDataSet(_sqlConnection, CommandType.Text,
                                                           SqlQuery.ViewPostByCategoryId, parameter);
                return dataSet.Tables[0];
            }
            catch (Exception)
            {
                
                throw;
            }
        }



    }
}
